/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import classes.Category;
import classes.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import logic.Main;

/**
 *
 * @author Ismail Sunni
 */
public final class database {

    public Connection _conn = null;
    public static String url = "jdbc:mysql://localhost:3306/";
    public static String dbName = "miss-ica";
    public static String driver = "com.mysql.jdbc.Driver";
    public static String userName = "root";
    public static String password = "";
    public Statement st = null;
    public ResultSet rs = null;
    public String strQuery;

    public database() {
        connect();
    }

    // base functions
    public void connect() {
        try {
            Class.forName(driver).newInstance();
            System.out.println("Connected to the database lalalla");
            _conn = DriverManager.getConnection(url + dbName, userName, password);
            if (_conn != null) {
                System.out.println("yahuuuu");
            }

        } catch (Exception e) {
        }
    }

    public void disconnect() {
        try {
            _conn.close();
            System.out.println("Disconnected from database");
        } catch (Exception e) {
        }
    }

    public User login(String sUsername, String sPassword) {
        try {
            User u;
            strQuery = "SELECT * FROM user WHERE username='" + sUsername + "' and  password='" + sPassword + "'";
            System.out.println(strQuery);
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);
            if (rs.next()) {
                u = getUser(sUsername);
                System.out.println("username : " + sUsername);
                return u;
            } else {
                return null;
            }
        } catch (Exception e) {
            System.out.println("gagal2");
            System.out.println(e.getMessage());
            return null;
        }
    }

    // add function area
    public void addUser(String sUsername, String sPassword, String sNama, String sEmail, String sPrivilege, boolean bBanned) {
        try {
            strQuery = "INSERT INTO user (username, password, nama, email, privilege, banned) VALUES ('" + sUsername + "', '" + sPassword + "', '" + sNama + "', '" + sEmail + "', '" + sPrivilege + "', '" + 0 + "')";
            if (_conn != null) {
                st = _conn.createStatement();
                int val = st.executeUpdate(strQuery);
                System.out.println("sukses add user");
                System.out.println(strQuery);
            } else {
                System.out.println("lontong bakmi");
            }

        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("gagal add user");
        }
    }

    public void addCategory(String sName, String sDesc, int nParetnID) {
        try {
            strQuery = "INSERT INTO kategori (name, desc, parent) VALUES ('" + sName + "', '" + sDesc + "', '" + nParetnID + "')";
            if (_conn != null) {
                st = _conn.createStatement();
                int val = st.executeUpdate(strQuery);
                System.out.println("sukses add category");
                System.out.println(strQuery);
            } else {
                System.out.println("lontong bakmi, gagal bikin kategori");
            }

        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("gagal add category");
        }
    }

    public void addFriend(String sUsername1, String sUsername2) {
        try {
            if (isFriend(sUsername1, sUsername2)) {
                strQuery = "INSERT INTO friend (username, friend) VALUES ('" + sUsername1 + "', '" + sUsername2 + "')";
                if (_conn != null) {
                    st = _conn.createStatement();
                    int val = st.executeUpdate(strQuery);
                    System.out.println("sukses add friend");
                    System.out.println(strQuery);
                } else {
                    System.out.println("lontong bakmi, gagal add friend");
                }
            }


        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("gagal add friend");
        }
    }

    // edit function area
    public void editMaxRoom(int nMaxRoom) {
        int nRecentMaxRoom = getMaxRoom();
        strQuery = "UPDATE 'setting' SET 'maxroom' = '" + nMaxRoom + "' WHERE 'maxroom = '" + nRecentMaxRoom + "'";
        try {
            st = _conn.createStatement();
            int val = st.executeUpdate(strQuery);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void editProfil(String sUsername, String sNew, int n) {
        try {
            if (n == 1) // update nama
            {
                strQuery = "UPDATE 'user' SET 'nama' = '" + sNew + "' WHERE 'username = '" + sUsername + "'";
            } else if (n == 2) // update password
            {
                strQuery = "UPDATE 'user' SET 'password' = '" + sNew + "' WHERE 'username = '" + sUsername + "'";
            } else if (n == 3) // update email
            {
                strQuery = "UPDATE 'user' SET 'email' = '" + sNew + "' WHERE 'username = '" + sUsername + "'";
            } else if (n == 4) // update privilege
            {
                strQuery = "UPDATE 'user' SET 'privilege' = '" + sNew + "' WHERE 'username = '" + sUsername + "'";
            } else {
                strQuery = "";
            }

            st = _conn.createStatement();
            int val = st.executeUpdate(strQuery);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void banUser(String sUsername, boolean bBanned) {
        int nBan = 0;
        if (bBanned == true)
        {
            nBan = 1;
        }
        try {
            strQuery = "UPDATE 'user' SET 'banned' = '" + nBan + "' WHERE 'username = '" + sUsername + "'";
            st = _conn.createStatement();
            int val = st.executeUpdate(strQuery);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void editCategory(String sName, String sDesc) {
        try {
            strQuery = "UPDATE 'kategori' SET 'nama' = '" + sDesc + "' WHERE 'name = '" + sName + "'";
            st = _conn.createStatement();
            int val = st.executeUpdate(strQuery);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    // get function area
    public ArrayList<User> getAllUser() {
        ArrayList<User> arrayUser = new ArrayList<User>();
        try {
            strQuery = "SELECT * FROM user";
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);
            while (rs.next()) {
                User newUser = new User();
                newUser.username = rs.getString("username");
                newUser.password = rs.getString("password");
                newUser.nama = rs.getString("nama");
                newUser.email = rs.getString("email");
                newUser.privilege = rs.getString("privilege");
                newUser.banned = rs.getBoolean("banned");
                newUser.online = false;
                newUser.lastMessageId = -1;
                arrayUser.add(newUser);
            }
            return arrayUser;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }

    public ArrayList<User> getArrayFriend(String sUsername) {
        ArrayList<User> arrayFriend = new ArrayList<User>();
        try {
            ArrayList<User> arrayUserFriend = new ArrayList<User>();
            for (int i = 0; i < arrayFriend.size(); ++i) {
                arrayUserFriend.add(getUser(arrayFriend.get(i).username));
            }
            return arrayUserFriend;

        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }

    public ArrayList<String> getFriends(String sUsername) {
        try {
            ArrayList<String> Friends = new ArrayList<String>();

            strQuery = "SELECT friend FROM friend WHERE username = '" + sUsername + "'";
            System.out.println(strQuery);
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);

            while (rs.next()) {
                Friends.add((String) rs.getObject(1));
            }
/*
            strQuery = "SELECT username FROM friend WHERE friend = '" + sUsername + "'";
            System.out.println(strQuery);
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);

            while (rs.next()) {
                Friends.add((String) rs.getObject(1));
            }
*/
            System.out.println("sukses getfriends");

            return Friends;

        } catch (SQLException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public ArrayList<Category> getCategory(int parent) {
        try {
            ArrayList<Category> Categories = new ArrayList<Category>();
            strQuery = "SELECT id,name FROM kategori WHERE parent = '" + parent + "'";
            System.out.println("ini query " + strQuery);
            st = _conn.createStatement();
            System.out.println("ini st " + st);
            rs = st.executeQuery(strQuery);
            System.out.println("ini rs " + rs);

            String categName;
            int categId;

            int count = 0;
            while (rs.next()) {
                categName = rs.getObject(2).toString();
                categId = Integer.parseInt(rs.getObject(1).toString());
                Category Kateg = new Category(categId, categName);
                Categories.add(Kateg);
                count++;
            }
            System.out.println("sukses getarraycategory");
            for (int i = 0; i < Categories.size(); ++i) {
                System.out.println(Categories.get(i).name);
                System.out.println(Categories.get(i).id);
            }
            return Categories;
        } catch (SQLException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public int getMaxRoom() {
        try {
            strQuery = "SELECT * FROM setting";
            System.out.println(strQuery);
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);
            if (rs.next()) {
                int maxRoom = rs.getInt("maxroom");
                return maxRoom;
            } else {
                System.out.println("gagal1");
                return 0;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return 0;
        }
    }

    public User getUser(String sUsername) {
        User u = Main.getUserFromName(sUsername);
        if (u == null) {
            System.out.println("user name : " + sUsername);
            if (_conn == null) {
                System.out.print("conn = null");
            } else {
                System.out.println("baik2 saja");
                try {
                    strQuery = "SELECT * FROM user WHERE username='" + sUsername + "'";
                    System.out.println(strQuery);
                    st = _conn.createStatement();
                    rs = st.executeQuery(strQuery);
                    if (rs.next()) {
                        u = new User();
                        u.username = sUsername;
                        u.password = rs.getString("password");
                        u.nama = rs.getString("nama");
                        u.email = rs.getString("email");
                        u.privilege = rs.getString("privilege");
                        u.banned = rs.getBoolean("banned");
                    } else {
                        System.out.println("rs user null");
                        return null;
                    }
                } catch (Exception e) {
                    System.out.println("exception getUSer");
                    System.out.println(e.getMessage());
                    return null;
                }
            }
        } else {
        }
        return u;
    }

    public String getPassword(String sUsername) {
        try {
            strQuery = "SELECT password FROM user WHERE username='" + sUsername + "'";
            System.out.println(strQuery);
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);
            if (rs.next()) {
                String sPassword = rs.getString("password");
                return sPassword;
            } else {
                System.out.println("gagal1");
                return "";
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return "";
        }
    }

    public String getEmail(String sUsername) {
        try {
            strQuery = "SELECT email FROM user WHERE username='" + sUsername + "'";
            System.out.println(strQuery);
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);
            if (rs.next()) {
                String sEmail = rs.getString("email");
                return sEmail;
            } else {
                System.out.println("gagal1");
                return "";
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return "";
        }
    }

    public String getPrivilege(String sUsername) {
        try {
            strQuery = "SELECT privilege FROM user WHERE username='" + sUsername + "'";
            System.out.println(strQuery);
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);
            if (rs.next()) {
                String sPrivilege = rs.getString("privilege");
                return sPrivilege;
            } else {
                System.out.println("gagal1");
                return "";
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return "";
        }
    }

    public boolean isBanned(String sUsername) {
        try {
            strQuery = "SELECT banned FROM user WHERE username='" + sUsername + "'";
            System.out.println(strQuery);
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);
            if (rs.next()) {
                boolean bBanned = rs.getBoolean("banned");
                return bBanned;
            } else {
                System.out.println("gagal1");
                return true;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return true;
        }
    }

    public boolean isUsernameExisted(String sUsername) {
        try {
            User u = getUser(sUsername);
            if (u == null) {
                return false;
            } else {
                return true;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return true;
        }
    }

    public boolean isFriend(String sUsername1, String sUsername2) {
        try {
            strQuery = "SELECT * FROM friend WHERE username='" + sUsername1 + "' AND friend='" + sUsername2 + "'";
            st = _conn.createStatement();
            rs = st.executeQuery(strQuery);
            if (rs.next()) {
                return true;
            }
            /*
            strQuery = "SELECT * FROM friend WHERE username='" + sUsername2 + "' AND friend='" + sUsername1 + "'";
            rs = st.executeQuery(strQuery);
            if (rs.next()) {
                return true;
            } */
            else {
                return false;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return false;
        }
    }

    // delete function area
    public void delUser(String sUsername) {
        strQuery = "DELETE FROM user WHERE 'username' = " + sUsername + "'";
        try {
            st = _conn.createStatement();
            int val = st.executeUpdate(strQuery);
            delAllFriend(sUsername);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void delFriend(String sUsername1, String sUsername2) {
        strQuery = "DELETE FROM friend WHERE 'username' = " + sUsername1 + "' AND 'friend' = " + sUsername2 + "'";
        String sQuery = "DELETE FROM friend WHERE 'username' = " + sUsername2 + "' AND 'friend' = " + sUsername1 + "'";
        try {
            st = _conn.createStatement();
            int val = st.executeUpdate(strQuery);
            val = st.executeUpdate(sQuery);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void delAllFriend(String sUsername) {
        strQuery = "DELETE FROM friend WHERE 'username' = " + sUsername + "' OR friend = '" + sUsername + "'";
        try {
            st = _conn.createStatement();
            int val = st.executeUpdate(strQuery);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public void delCategory() {
        // susah ini gan
    }
}
